﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Text;
using ReflectionStudio.Core.Events;
using INTEX.DataBase.Tools;
namespace WPFExcelReport
{

    public class JLTVK02JPData : BaseFunction
    {
        
        public JLTVK02JPData()
            : base()
        {

        }
        public JLTVK02JPData(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {
        }

        public DataSet GetVK02(DataRow dwABPOS)
        {
            DataSet Result;
            DataSet Return = new DataSet();
            string TableName = "WASTAMM";
            string POSTYPE = dwABPOS["POSTYPE"].ToString().Trim();
            string KRS = dwABPOS["KRS"].ToString().Trim();
            string LFD = dwABPOS["LFD"].ToString().Trim();
            string CUPO = dwABPOS["CUPO"].ToString().Trim();
            string FROMDNDATE = dwABPOS["FROMDNDATE"].ToString().Trim();
            string ENDDNDATE = dwABPOS["ENDDNDATE"].ToString().Trim();
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT A.VKPAUFKRS||'-'||TO_NUMBER(A.VKPAUFLFD) AS ORDERNO,TO_NUMBER(A.VKPAUFPOS) AS ITEM, "
                 + " A.VKPNEUBEN,A.VKPNEUDAT,TO_NUMBER(A.VKPKUNDNR) AS BRANDCU,A.VKPSACHBE,TRIM(H.HRNNAME) AS SCHB, "
                 + " A.VKPPOSTYP,A.VKPKOLL,TRIM(A.VKPART) AS VKPART,TRIM(A.VKPFARBE) AS VKPFARBE,A.VKPMENGE1*0.01 AS QTY,VKPMEEH, "
                 + " SUM(TRUNC(A.VKPMENGE1*0.01*VKPMEEHFKT*0.000001))OVER(PARTITION BY A.VKPAUFKRS,A.VKPAUFLFD,A.VKPFARBE) AS MQTY, "
                 + " A.VKPPREIS*0.001 AS PRICS,A.VKPPREH,A.VKPWUTERM,A.VKPLIEFTERM,A.VKPKKOLL, "
                 + " NVL(A3.REMARK,A.VKPZUSATZ) AS REFERENCES,A.VKPKDKOMM,A.VKPWAEHRSL, "
                 + " B.VAAARTGRP AS ARTGRP,(select substr(sddaten,2,20) "
                 + " from sddat where sdsa = '0178' and substr(sdkey,24,1)='V' "
                 + " and substr(sddaten,1,1)='A' and substr(sdkey,3,3) = B.VAAARTGRP ) as ARTNAME, "
                 + " VAAALTERNAT AS STR,(select substr(sddaten,52,10) "
                 + " from sddat where sdsa = '0734' and substr(sddaten,1,1)='A'  "
                 + " and substr(sdkey,3,1) = VAAALTERNAT ) as STRNAME, "
                 + " VAABRTVON*0.01 AS VALIDWID,nvl((SELECT MAX(TRIM(T2.TXTKTEXT)) AS YARN "
                 + " from txtind T1,TXTKON T2 where T1.txtitxtart = 'VAD' "
                 + " AND T1.TXTIMANDANT = T2.TXTKMANDANT AND T1.TXTIPIND = T2.TXTKPIND "
                 + " AND A.VKPKOLL || TRIM(A.VKPART) = TRIM(T1.TXTISIND)),B.VAABEZ) AS DESCR, "
                 + " to_number(DECODE(vaagesant1,'0','',vaagesant1))||trim(VAAGESBEZ1) "
                 + " ||to_number(DECODE(vaagesant2,0,'',vaagesant2))||TRIM(VAAGESBEZ2) "
                 + " ||to_number(DECODE(vaagesant3,0,'',vaagesant3))||TRIM(VAAGESBEZ3) "
                 + " ||to_number(DECODE(vaagesant4,0,'',vaagesant4))||TRIM(VAAGESBEZ4) "
                 + " ||to_number(DECODE(vaagesant5,0,'',vaagesant5))||TRIM(VAAGESBEZ5) as COMPOSITION,  "
                 + " NVL(TRIM(D.KDKNAME),trim(D.KDKUNDNA)) as KUNDNA,TRIM(D.KDNAME1) AS BRANDNAME, "
                 + " TRIM(NVL(NVL(A2.KDANAME1,A1.KDANAME1),D.KDKNAME)) PAYCU, "
                 + " C.VAFBESCHICH,(select substr(sddaten,2,20) "
                 + " from sddat where sdsa = '0275' and substr(sddaten,1,1)='A'  "
                 +" and substr(sdkey,3,3) = SUBSTR(C.VAFBESCHICH,1,3) ) as FINISHING," 
                 + " SUBSTR(A.VKPLIEFTERM,5,2) as mon,SUBSTR(A.VKPLIEFTERM,7,2) as dd, "
                 + " (CASE WHEN SUBSTR(A.VKPLIEFTERM,7,2)<=10 THEN 'EARLY'  "
                 + "   WHEN SUBSTR(A.VKPLIEFTERM,7,2)>20 THEN 'LAST' "
                 + "   WHEN TRIM(SUBSTR(A.VKPLIEFTERM,7,2)) IS NULL THEN '' ELSE 'MID' END )as DA, "
                 + " (SELECT max(REPLACE(REPLACE(TXTKTEXT,'½»»õ·¶Î§:',''),'¡£','')) "
                 + " FROM TXTIND X,TXTKON Y WHERE X.TXTIMANDANT = Y.TXTKMANDANT "
                 + " AND X.TXTITXTART = 'VKT' AND X.TXTIPIND = Y.TXTKPIND "
                 + " AND X.TXTISIND LIKE '%000LIEF%' AND Y.TXTKTEXT LIKE '½»»õ·¶Î§:%' "
                 + " AND SUBSTR(X.TXTISIND,1,8) = A.VKPAUFKRS||A.VKPAUFLFD) AS DELNOTE, "
                 + " trim(I.vakbkdartnr) AS KDART,trim(I.vakbkdartbez) AS KDARTBEZ,trim(I.vakbkdfarbnr) AS KDARTCOLOR "
                 + " FROM VKPOS A,VAART B,VAFARBEN C,KDSTAMM D,HRSTAMM H,VAKDBEZ I, "
                 + " (SELECT A.VKAMANDANT,A.VKASA,A.VKAAUFKRS,A.VKAAUFLFD,B.KDANAME1  "
                 + " FROM VKADR A,KUNDADR B "
                 + " WHERE A.VKAMANDANT = B.KDAMANDANT AND A.VKAKUNDNR3 = B.KDAKUNDNR "
                 + " AND A.VKAADRSL3 = B.KDAADRSL AND A.VKAAUFPOS='000') A1, "
                 + " (SELECT A.VKAMANDANT,A.VKASA,A.VKAAUFKRS,A.VKAAUFLFD,A.VKAAUFPOS,B.KDANAME1  "
                 + " FROM VKADR A,KUNDADR B "
                 + " WHERE A.VKAMANDANT = B.KDAMANDANT AND A.VKAKUNDNR3 = B.KDAKUNDNR "
                 + " AND A.VKAADRSL3 = B.KDAADRSL AND A.VKAAUFPOS >'000') A2, "
                 + " (SELECT DISTINCT TRIM(SUBSTR(A.TXTISIND,1,8)) AS ORDERNO, "
                 + " LPAD(SUBSTR(TXTKTEXT,1,INSTR(REPLACE(TXTKTEXT,'¡£','.'),'.',1)-1),3,'0') AS ITEM, "
                 + " STRAGG(TRIM(SUBSTR(TXTKTEXT,INSTR(REPLACE(TXTKTEXT,'¡£','.'),'.',1)+1))) "
                 + " OVER(PARTITION BY SUBSTR(TXTKTEXT,1,INSTR(REPLACE(TXTKTEXT,'¡£','.'),'.',1)-1), "
                 + " SUBSTR(A.TXTISIND,1,8)) AS REMARK "
                 + " FROM TXTIND A,TXTKON B WHERE A.TXTIMANDANT = B.TXTKMANDANT "
                 + " AND TXTITXTART = 'VKT' AND A.TXTIPIND = B.TXTKPIND "
                 + " AND A.TXTISIND LIKE '%000IVVT%') A3  "
                 + " WHERE A.VKPSA <> 'AJ' AND A.VKPAUFART = 'NA' "
                 + " AND A.VKPMANDANT = B.VAAMANDANT AND A.VKPKOLL = B.VAAKOLL "
                 + " AND A.VKPART = B.VAAART AND A.VKPMANDANT = C.VAFMANDANT "
                 + " AND A.VKPKOLL = C.VAFKOLL AND A.VKPART = C.VAFART AND A.VKPFARBE = C.VAFFARBE "
                 + " AND A.VKPMANDANT = D.KDMANDANT AND A.VKPKUNDNR = D.KDKUNDNR "
                 + " AND A.VKPMANDANT = H.HRMANDANT AND A.VKPSACHBE = H.HRBENID "
                 + " AND A.VKPMANDANT = I.VAKBMANDANT(+) AND A.VKPKOLL = I.VAKBKOLL(+) "
                 + " AND A.VKPART = I.VAKBART(+) AND A.VKPKUNDNR = I.VAKBKUNDNR(+) AND A.VKPFARBE = I.VAKBFARBE(+) "
                 + " AND A.VKPMANDANT = A1.VKAMANDANT(+) AND A.VKPSA = A1.VKASA(+) "
                 + " AND A.VKPAUFKRS = A1.VKAAUFKRS(+) AND A.VKPAUFLFD = A1.VKAAUFLFD(+) "
                 + " AND A.VKPMANDANT = A2.VKAMANDANT(+) AND A.VKPSA = A2.VKASA(+) "
                 + " AND A.VKPAUFKRS = A2.VKAAUFKRS(+) AND A.VKPAUFLFD = A2.VKAAUFLFD(+) "
                 + " AND A.VKPAUFPOS = A2.VKAAUFPOS(+)  "
                 + " AND A.VKPAUFKRS||A.VKPAUFLFD = A3.ORDERNO(+) "
                 + " AND A.VKPAUFPOS = A3.ITEM(+) "
                 + " AND (A.VKPPOSTYP like '" + POSTYPE + "%' OR '" + POSTYPE + "' = 'AAA' ) "
                 + " AND (A.VKPAUFKRS = '" + KRS + "' OR '" + KRS + "' = 'AA' ) "
                 + " AND (A.VKPAUFLFD = '" + LFD + "' OR '" + LFD + "' = '000000' ) "
                 + " AND (trim(A.VKPKDKOMM) = '" + CUPO + "' OR '" + CUPO + "' = 'AAAAAAAAAAAAAAA' ) "
                 + " AND (A.VKPLIEFTERM >= " + FROMDNDATE + " AND  A.VKPLIEFTERM <= " + ENDDNDATE + ") "                    
                 + " ORDER BY 1,2  " ;

                Result = Connect2Proxy(orlReadCommand);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = TableName;
                    byte[] b;
                    string value;
                    foreach (DataRow dw in Result.Tables[TableName].Rows)
                    {
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["SCHB"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["SCHB"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["REFERENCES"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["REFERENCES"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["ARTNAME"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["ARTNAME"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["STRNAME"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["STRNAME"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["DESCR"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["DESCR"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["KUNDNA"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["KUNDNA"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["BRANDNAME"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["BRANDNAME"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["PAYCU"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["PAYCU"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["FINISHING"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["FINISHING"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["KDART"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["KDART"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["KDARTBEZ"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["KDARTBEZ"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["KDARTCOLOR"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["KDARTCOLOR"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["DELNOTE"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["DELNOTE"] = value;
                        value = dw["DA"].ToString().Trim();
                        if (value == "EARLY")
                        {
                            value = "上旬";
                        }
                        if (value == "MID")
                        {
                            value = "中旬";
                        }
                        if (value == "LAST")
                        {
                            value = "下旬";
                        }
                        dw["DA"] = value;
                    }
                }
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return Return;
        }       
        public DataSet BuildJLTVK02JPData(DataRow dwUI)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                setResult = GetVK02(dwUI);
                setReturn.Merge(setResult);                
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
    }
}